﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using ToolsLayer;

namespace Webcar.Car
{
    public partial class CarSummary : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (this.IsPostBack) return;
            this.RadioButton1.Checked = true;
        }

        void Query(string sql,string sql2)
        {
            this.GridView1.Columns.Clear();
            DataTable table = DB.ExecuteDataset(sql).Tables[0];
            table.PrimaryKey = new DataColumn[] { table.Columns["dept_code"], table.Columns["col"] };

            DataTable table_dept = DB.ExecuteDataset("select dept_code,dept_name from dept where isnull(del_flag,'F')='F'").Tables[0];
            DataTable table_sort = DB.ExecuteDataset(sql2).Tables[0];

            DataTable data = new DataTable();
            data.Columns.Add("部门名称");
            BoundField field = new BoundField();
            field.DataField = "部门名称";
            field.ItemStyle.HorizontalAlign = HorizontalAlign.Left;
            field.ItemStyle.Width = Unit.Pixel(200);
            field.HeaderText = "部门名称";
            this.GridView1.Columns.Add(field);
            Dictionary<string, string> col_map = new Dictionary<string, string>();
            int pos = 0;
            foreach (DataRow sort_row in table_sort.Rows)
            {
                string col_value = sort_row["col"].ToString();
                string col_name = "col_" + (++pos).ToString() ;
                data.Columns.Add(col_name, typeof(int));
                BoundField field2 = new BoundField();
                field2.DataField = col_name;
                field2.HeaderText = col_value;
                this.GridView1.Columns.Add(field2);
                col_map[col_value] = col_name;
            }

            
            BoundField field3 = new BoundField();
            field3.DataField = "合计";
            field3.HeaderText = "合计";
            this.GridView1.Columns.Add(field3);
            data.Columns.Add("合计", typeof(int));

            foreach (DataRow row in table_dept.Rows)
            {
                string dept = row["dept_code"].ToString();
                string dept_name = row["dept_name"].ToString();
                int sum = 0;
                DataRow newrow = data.NewRow();

                newrow["部门名称"] = dept_name;
                foreach (DataRow sort_row in table_sort.Rows)
                {
                    string col_value = sort_row["col"].ToString();
                    string col_name = col_map[col_value];
                    DataRow datarow = table.Rows.Find(new object[] { dept, col_value });
                    if (datarow != null)
                    {
                        newrow[col_name] = datarow["nums"];
                        sum += int.Parse(datarow["nums"].ToString());
                    }
                    else
                    {
                        Console.WriteLine("NULL");
                    }
                }
                newrow["合计"] = sum;
                data.Rows.Add(newrow);
            }
            //合计
            DataRow sum_row = data.NewRow();
            sum_row["部门名称"] = "合计：";
            foreach (DataColumn column in data.Columns)
            {
                if (column.ColumnName == "部门名称") continue;
                sum_row[column.ColumnName] = data.Compute("sum("+column.ColumnName+")", "");
            }
            data.Rows.Add(sum_row);
            this.GridView1.AutoGenerateColumns = false;
            this.GridView1.DataSource = data;
            this.GridView1.DataBind();

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            string sql = "";
            string sql2 = "";
            if (this.RadioButton1.Checked)
            {
                sql = @"select dept_code,dept_name,isnull(car_sort_name,'无车型') col,count(1) nums from v_bs_car_record  where dept_delflag='F' and is_use='T'
                        group by dept_code,dept_name,isnull(car_sort_name,'无车型')";
                sql2 = "select distinct car_sort_name col from v_bs_car_record  where dept_delflag='F'  union select '无车型'";
            }
            if (this.RadioButton2.Checked)
            {
                sql = @"select dept_code,dept_name,isnull(assdept_cn,'无归属') col,count(1) nums from v_bs_car_record  where dept_delflag='F' and is_use='T'
                        group by dept_code,dept_name,isnull(assdept_cn,'无归属')";
                sql2 = "select base_code,base_name col from dbo.v_base_code where base_kind ='C' union select 999999,'无归属' order by base_code";
            }
            if (this.RadioButton3.Checked)
            {
                sql = @"select dept_code,dept_name,isnull(car_use_cn,'无用途') col,count(1) nums from v_bs_car_record  where dept_delflag='F' and is_use='T'
                        group by dept_code,dept_name,isnull(car_use_cn,'无用途')";
                sql2 = "select base_code,base_name col from v_car_use union select 999999,'无用途'  order by base_code";
            }
            if (this.RadioButton4.Checked)
            {
                string exp = "isnull(replace(replace(replace(replace(a.COLOR,'色',''),'/',''),'\',''),' ',''),'无')";
                sql = @"select b.DEPT_CODE,b.DEPT_NAME,{0} col,COUNT(1) nums from bs_car_record a,DEPT b where 
                        a.DEPT_CODE=b.DEPT_CODE and ISNULL(b.del_flag,'F')='F'
                        and a.is_use='T'
                        group by b.DEPT_CODE,b.DEPT_NAME,{0}";
                sql = string.Format(sql, exp);
                sql2 = "select distinct {0} col from bs_car_record a";
                sql2 = string.Format(sql2, exp);
                
            }
            if (this.RadioButton5.Checked)
            {
                sql = @"select dept_code,dept_name,
                        case when isnull(guest_num,0)= 0 then '未录入'
                        when  isnull(guest_num,0) =1 then '1座' 
                        when  isnull(guest_num,0) =2 then '2座' 
                        when  isnull(guest_num,0) =3 then '3座' 
                        when  isnull(guest_num,0) =4 then '4座' 
                        when  isnull(guest_num,0) =5 then '5座' 
                        
                        when  isnull(guest_num,0) between 6 and 9 then '6到9座' 
                        when  isnull(guest_num,0) between 10 and 15 then '10到15座' 
                        when  isnull(guest_num,0) between 16 and 26 then '16到26座' 
                        when  isnull(guest_num,0) >26 then '26座以上' 
                        end col,COUNT(1) nums
                        from v_bs_car_record where dept_delflag='F' and is_use='T'
                        group by dept_code,dept_name,case when isnull(guest_num,0)= 0 then '未录入'
                        when  isnull(guest_num,0) =1 then '1座' 
                        when  isnull(guest_num,0) =2 then '2座' 
                        when  isnull(guest_num,0) =3 then '3座' 
                        when  isnull(guest_num,0) =4 then '4座' 
                        when  isnull(guest_num,0) =5 then '5座' 
                        
                        when  isnull(guest_num,0) between 6 and 9 then '6到9座' 
                        when  isnull(guest_num,0) between 10 and 15 then '10到15座' 
                        when  isnull(guest_num,0) between 16 and 26 then '16到26座' 
                        when  isnull(guest_num,0) >26 then '26座以上'  end";
                sql2 = @"select 0 orders,'1座' col union select 1,'2座' union select 2,'3座' union select 3,'4座' union select 4,'5座' 
                    union select 6,'6到9座' union select 7,'10到15座' union select 8,'16到26座' union select 9,'26座以上' union select 10,'未录入' order by orders";
            }

            if (this.RadioButton6.Checked)
            {
                sql = @"select 
                        dept_code,dept_name,
                        case when carry_weight= 0 then '未录入'
                        when  carry_weight>0 and carry_weight<0.35 then '0.35吨以下' 
                        when  carry_weight>= 0.35 and carry_weight< 1 then '0.35到1吨'
                         when  carry_weight>= 1 and carry_weight< 2 then '1到2吨'
                        when  carry_weight>= 2 and carry_weight<5 then '2到5吨' 
                        when  carry_weight>= 5 and carry_weight<=12 then '6到12吨' 
                        when  carry_weight >12 then '12吨以上'  end  
                        col,COUNT(1) nums
                        from (
                        select dept_code,dept_name, isnull(case when carry_weight>100 then carry_weight/1000 else carry_weight end,0) carry_weight
                        from v_bs_car_record where  dept_delflag='F' and is_use='T') as a
                        group by dept_code,dept_name,case when carry_weight= 0 then '未录入'
                        when  carry_weight>0 and carry_weight<0.35 then '0.35吨以下' 
                        when  carry_weight>= 0.35 and carry_weight< 1 then '0.35到1吨'
                         when  carry_weight>= 1 and carry_weight< 2 then '1到2吨'
                        when  carry_weight>= 2 and carry_weight<5 then '2到5吨' 
                        when  carry_weight>= 5 and carry_weight<=12 then '6到12吨' 
                        when  carry_weight >12 then '12吨以上'  end  ";
                sql2 = @"select 0 orders,'0.35吨以下' col union select 1,'0.35到1吨' union 
                select 2,'1到2吨' union select 3,'2到5吨' union select 4,'6到12吨' union select 5 ,'12吨以上' union select 6,'未录入' order by orders"; ;
            }
            
            //

            //0-5  6-9 10-15 17-26 26+
            //0.35-1 1-2 2-5 6-12 12+  

            this.Query(sql,sql2);
        }
    }
}
